﻿
Imports System.Data.Common
Imports LibMultiPlug.Data.SQLite.Utils.DataUtils

Namespace Services

    Public Class ReportService : Inherits LibMultiPlug.Core.Services.ServiceDefinitions.ReportService


        Private Function GetLastInsertId() As Integer

            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()

            cmd.CommandText = "SELECT last_insert_rowid()"

            Return CInt(_dataService.ExecuteScalar(cmd))

        End Function


        Public Sub New(ByVal dataService As DbDataLib.DbDataService)

            MyBase.New(dataService)

        End Sub

        Protected Overloads Overrides Sub InternalDeleteReport(ByVal id As Integer)
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.Append("DELETE FROM reports WHERE r_id = @p_id")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, id))

            _dataService.ExecuteNonQuery(cmd)

        End Sub

        Protected Overloads Overrides Sub InternalDeleteReport(ByVal r As LibMultiPlug.Core.Reporting.Report)
            Call InternalDeleteReport(r.Id)
        End Sub

        Public Overrides Function GetReport(ByVal id As Integer) As LibMultiPlug.Core.Reporting.Report
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder
            Dim dt As DataTable
            Dim r As LibMultiPlug.Core.Reporting.Report = Nothing

            sb.AppendLine("SELECT * FROM reports WHERE r_id = @p_id")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, id))

            dt = _dataService.GetDataTable(cmd)

            For i As Integer = 0 To dt.Rows.Count - 1 Step 1

                r = New LibMultiPlug.Core.Reporting.Report

                r.Id = DbNull2Nothing(Of Integer)(dt.Rows(i)!r_id)
                r.Name = DbNull2Nothing(Of String)(dt.Rows(i)!r_name)
                r.Statement = DbNull2Nothing(Of String)(dt.Rows(i)!r_statement)
                r.ConnectionName = DbNull2Nothing(Of String)(dt.Rows(i)!r_connection_name)
                r.IdColumn = DbNull2Nothing(Of String)(dt.Rows(i)!r_id_column)
                r.StartType = DbNull2Nothing(Of String)(dt.Rows(i)!r_start_type)


                Exit For

            Next

            Return r

        End Function

        Public Overrides Function GetReports() As System.Collections.Generic.List(Of LibMultiPlug.Core.Reporting.Report)
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder
            Dim dt As DataTable
            Dim lor As New List(Of LibMultiPlug.Core.Reporting.Report)

            sb.AppendLine("SELECT * FROM reports")

            cmd.CommandText = sb.ToString()

            dt = _dataService.GetDataTable(cmd)

            For i As Integer = 0 To dt.Rows.Count - 1 Step 1

                Dim r As New LibMultiPlug.Core.Reporting.Report

                r.Id = DbNull2Nothing(Of Integer)(dt.Rows(i)!r_id)
                r.Name = DbNull2Nothing(Of String)(dt.Rows(i)!r_name)
                r.Statement = DbNull2Nothing(Of String)(dt.Rows(i)!r_statement)
                r.ConnectionName = DbNull2Nothing(Of String)(dt.Rows(i)!r_connection_name)
                r.IdColumn = DbNull2Nothing(Of String)(dt.Rows(i)!r_id_column)
                r.StartType = DbNull2Nothing(Of String)(dt.Rows(i)!r_start_type)

                lor.Add(r)

            Next

            Return lor

        End Function

        Protected Overrides Sub InternalSaveReport(ByRef r As LibMultiPlug.Core.Reporting.Report)
            If Not UpdateReport(r) Then InsertReport(r)
        End Sub

        Protected Function InsertReport(ByRef r As LibMultiPlug.Core.Reporting.Report) As Boolean

            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("INSERT INTO reports(r_name, r_statement, r_connection_name, r_start_type, r_id_column) ")
            sb.AppendLine("VALUES(@p_name, @p_statement, @p_connection_name, @p_start_type, @p_id_column); ")
            sb.AppendLine("SELECT last_insert_rowid(); ")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_name", DbType.String, r.Name))
            cmd.Parameters.Add(_dataService.CreateParameter("p_statement", DbType.String, r.Statement))
            cmd.Parameters.Add(_dataService.CreateParameter("p_connection_name", DbType.String, r.ConnectionName))
            cmd.Parameters.Add(_dataService.CreateParameter("p_start_type", DbType.String, r.StartType))
            cmd.Parameters.Add(_dataService.CreateParameter("p_id_column", DbType.String, r.IdColumn))

            r.Id = DbNull2Nothing(Of Integer)(_dataService.ExecuteScalar(cmd))

            Return (r.Id > 0)

        End Function

        Protected Function UpdateReport(ByRef r As LibMultiPlug.Core.Reporting.Report) As Boolean

            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("UPDATE reports SET r_name = @p_name, r_statement = @p_statement, r_connection_name = @p_connection_name, ")
            sb.AppendLine("r_start_type = @p_start_type, r_id_column = @p_id_column  ")
            sb.AppendLine("WHERE r_id = @p_id ")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_name", DbType.String, r.Name))
            cmd.Parameters.Add(_dataService.CreateParameter("p_statement", DbType.String, r.Statement))
            cmd.Parameters.Add(_dataService.CreateParameter("p_connection_name", DbType.String, r.ConnectionName))
            cmd.Parameters.Add(_dataService.CreateParameter("p_start_type", DbType.String, r.StartType))
            cmd.Parameters.Add(_dataService.CreateParameter("p_id_column", DbType.String, r.IdColumn))
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, r.Id))

            Return (_dataService.ExecuteNonQuery(cmd) > 0)

        End Function

        Public Overrides Function GetReportsForList() As System.Collections.Generic.List(Of LibMultiPlug.Core.Reporting.ReportListEntry)
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder
            Dim dt As DataTable
            Dim lor As New List(Of LibMultiPlug.Core.Reporting.ReportListEntry)

            sb.AppendLine("SELECT r_id, r_name FROM reports")

            cmd.CommandText = sb.ToString()

            dt = _dataService.GetDataTable(cmd)

            For i As Integer = 0 To dt.Rows.Count - 1 Step 1

                Dim r As New LibMultiPlug.Core.Reporting.ReportListEntry

                r.Id = DbNull2Nothing(Of Integer)(dt.Rows(i)!r_id)
                r.Name = DbNull2Nothing(Of String)(dt.Rows(i)!r_name)

                lor.Add(r)

            Next

            Return lor
        End Function


    End Class

End Namespace
